package com.xiaolin.admin.utils.excel;

import com.xiaolin.admin.exception.ServiceException;
import lombok.Data;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFCell;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.nio.file.Files;
import java.nio.file.Paths;
import java.util.*;

/**
 * @author xiaolin
 * @description 描述
 * @since 2024/10/24
 */
@Data
public class BigDataExcelStableExportUtil {
    //标题
    private String title;
    // 页签名
    private String sheetName = "Sheet";
    //表头
    private String[][] headTitles;
    //表头key
    private String[] headKeys;
    //需要填充的数据信息
    private List<Map<String, Object>> data;

    private int rowAccessWindowSize = 1000;

    public SXSSFWorkbook createWb() {
        // 校验参数
        checkConfig();
        // 创建工作簿
        return new SXSSFWorkbook(rowAccessWindowSize);
    }

    public SXSSFSheet creatSheet(SXSSFWorkbook wb) {
        // 创建页签
        SXSSFSheet sheet = wb.createSheet(StringUtils.isBlank(sheetName) ? "Sheet" : sheetName);
        int headerIndex = 0;
        if (StringUtils.isNotBlank(title)) {
            headerIndex = 1;
            // 创建标题行
            createTitle(sheet);
        }
        // 创建表头
        createHeader(sheet, headTitles, headerIndex);
        return sheet;
    }

    public void createRows(SXSSFSheet sheet, List<Map<String, Object>> data, int initialRow) {
        // 行迭代
        for (int rowNum = initialRow; rowNum < initialRow + data.size(); rowNum++) {
            SXSSFRow row = sheet.createRow(rowNum);
            Map<String, Object> map = data.get(rowNum - initialRow);
            //加载列数据
            for (int colNum = 0; colNum < headKeys.length; colNum++) {
                String key = headKeys[colNum];
                SXSSFCell cell = row.createCell(colNum);
                Object valueObject = map.get(key);
                cell.setCellValue(!isEmpty(valueObject) ? valueObject.toString() : "");
            }
        }
    }

    public void exportFile(SXSSFWorkbook wb, HttpServletResponse response) {
        //导出数据
        OutputStream os = null;
        try {
            String headerValue = "attachment; filename=hello.xlsx";
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-Disposition", headerValue);
            os = response.getOutputStream();
            wb.write(os);
            os.flush();
            os.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new ServiceException("导出Excel出现严重异常，异常信息：" + e.getMessage());
        }
    }

    public void exportFile(SXSSFWorkbook wb) {
        //导出数据
        OutputStream os;
        try {
            os = Files.newOutputStream(Paths.get("D:\\testFile\\" + new Date().getTime() + ".xlsx"));
            wb.write(os);
            os.flush();
            os.close();
            wb.close();
        } catch (Exception e) {
            e.printStackTrace();
            throw new ServiceException("导出Excel出现严重异常，异常信息：" + e.getMessage());
        }
    }


    /**
     * 构建表头
     *
     * @param sheet
     * @param headTitles
     */
    protected void createHeader(SXSSFSheet sheet, String[][] headTitles, int headerIndex) {
        //合并数
        int mergerNum = 0;
        //给单元格设置值
        for (int i = 0; i < headTitles.length; i++) {
            int rowIndex = i + headerIndex;
            SXSSFRow mergeRow = sheet.createRow(rowIndex);
            for (int j = 0; j < headTitles[i].length; j++) {
                SXSSFCell cell = mergeRow.createCell(j);
                cell.setCellValue(headTitles[i][j]);
            }
        }
        Map<Integer, List<Integer>> mergeMap = new HashMap<>();   // 合并行时要跳过的行列
        //合并列
        for (int i = 0; i < headTitles[headTitles.length - 1].length; i++) {
            if ("".equals(headTitles[headTitles.length - 1][i])) {
                for (int j = headTitles.length - 2; j >= 0; j--) {
                    int rowIndex = j + headerIndex;
                    if (!"".equals(headTitles[j][i])) {
                        sheet.addMergedRegion(new CellRangeAddress(rowIndex, headTitles.length - 1 + headerIndex, i, i)); // 合并单元格
                        break;
                    } else {
                        if (mergeMap.containsKey(j)) {
                            List<Integer> list = mergeMap.get(j);
                            list.add(i);
                            mergeMap.put(j, list);
                        } else {
                            List<Integer> list = new ArrayList<>();
                            list.add(i);
                            mergeMap.put(j, list);
                        }
                    }
                }
            }
        }
        //合并行
        for (int i = 0; i < headTitles.length - 1; i++) {
            int rowIndex = i + headerIndex;
            for (int j = 0; j < headTitles[i].length; j++) {
                List<Integer> list = mergeMap.get(i);
                if (list == null || !list.contains(j)) {
                    if ("".equals(headTitles[i][j])) {
                        mergerNum++;
                        if (mergerNum != 0 && j == (headTitles[i].length - 1)) {
                            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, j - mergerNum, j)); // 合并单元格
                            mergerNum = 0;
                        }
                    } else {
                        if (mergerNum != 0) {
                            sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, j - mergerNum - 1, j - 1)); // 合并单元格
                            mergerNum = 0;
                        }
                    }
                }
            }
        }
    }

    /**
     * 构建标题
     *
     * @param sheet
     */
    protected void createTitle(SXSSFSheet sheet) {
        //在第0行创建表标题
        SXSSFRow titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(30); //行高
        SXSSFCell cellValue = titleRow.createCell(0);
        cellValue.setCellValue(title);
        int lastIndex = headTitles[0].length - 1;
        for (int i = 1; i <= lastIndex; i++) {
            // 创建被合并的单元格
            titleRow.createCell(i);
        }
        // 合并标题行单元格
        sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, lastIndex));
    }

    /**
     * 判断对象是否为空
     *
     * @param obj
     * @return
     */
    public boolean isEmpty(Object obj) {
        if (Objects.isNull(obj)) {
            return true;
        } else if (obj instanceof String) {
            String objStr = (String) obj;
            if (StringUtils.isBlank(objStr)) {
                return true;
            }
            return "null".equalsIgnoreCase(objStr.trim());
        }
        return false;
    }

    /**
     * 校验参数
     *
     * @throws IOException
     */
    protected void checkConfig() {
        if (headTitles == null || headTitles.length == 0) {
            throw new ServiceException("表头不能为空");
        }
        if (headKeys == null || headKeys.length == 0) {
            throw new ServiceException("表头key不能为空");
        }
        if (headTitles[0].length != headKeys.length) {
            throw new ServiceException("表头与表头key长度不一致");
        }
    }
}
